package com.PopulateDB;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Date;

public class MySQLDBPopulator extends DBPopulator{
	private Connection conn = null;
	
	public MySQLDBPopulator(String fileName){
		super(fileName);
	}
	
	protected void initConnection(){
		try {
			Class.forName(this.getDriver()).newInstance();
			this.conn = DriverManager.getConnection(this.getURL(), this.getUsername(), this.getPassword());
			//return this.conn;
		}
		catch(Exception e){
			System.err.println("MySQLDBPopulator (initConnection): Could not open a new database connection to " + this.getURL());
			e.printStackTrace();
			//return null;
		}
	}
	
	public Connection getConnection(){
		if(this.conn != null){
			return this.conn;	
		}
		else{
			initConnection();
			
			//return initConnection();
		}
		
		return this.conn;
	}
	
	public void closeConnection(){
		if(this.conn != null){
				// close the connection
				try{
					this.conn.close();
				}
				catch(Exception e){
					System.err.println("MySQLDBPopulator (closeConnection): Could not close database connection");
					e.printStackTrace();
				}	
		}
	}
	
	public boolean dropAddUsers(){
		boolean deleted = false;
		Connection conn = this.getConnection();
		
		if(conn != null){
			try{
				PreparedStatement dropStatement = conn.prepareStatement("DROP TABLE IF EXISTS `users`");
				
				PreparedStatement createStatement = conn.prepareStatement("CREATE TABLE IF NOT EXISTS `users` (" +
				  "`id` int(10) unsigned NOT NULL AUTO_INCREMENT, " +
				  "`firstname` varchar(20) DEFAULT NULL, " +
				  "`lastname` varchar(20) DEFAULT NULL, " +
				  "`username` varchar(20) NOT NULL, " +
				  "`password` varchar(20) NOT NULL, " +
				  "`authtoken` varchar(16) DEFAULT NULL, " +
				  "`email` varchar(50) NOT NULL, " +
				  "`rating` int(11) DEFAULT NULL, " +
				  "`creationDate` datetime DEFAULT NULL, " +
				  "`street` varchar(50) NOT NULL, " +
				  "`town` varchar(20) NOT NULL, " +
				  "`zip` varchar(10) NOT NULL, " +
				  "`state` int(10) NOT NULL, " +
				  "PRIMARY KEY (`id`), " +
				  "UNIQUE KEY `usernamePasswordIndex` (`username`,`password`) " +
				  ") ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1");
				
				dropStatement.executeUpdate();
				createStatement.executeUpdate();
				
				deleted = true;
			}
			catch(Exception e){
				System.err.println("MySQLDBPopulator (deleteUsers): Could not delete the users");
				e.printStackTrace();
			}
		}
		else{
			System.err.println("MySQLDBPopulator (deleteUsers): The database connection is not open");
		}
		
		return deleted;
	}
	
	public boolean insertUser(String firstName, String lastName, String username, String password, String authToken, String email, int rating, String street, String town, String zip, int state){
		boolean inserted = false;
		Connection conn = this.getConnection();
		
		if(conn != null){
			try{
				// Create the SQL statement to insert the user
				PreparedStatement statement = conn.prepareStatement("INSERT INTO `users` (firstname, lastname, username, password, authToken, email, rating, street, town, zip, state, creationDate) "
						+ "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, NOW())");
				statement.setString(1, firstName);
				statement.setString(2, lastName);
				statement.setString(3, username);
				statement.setString(4, password);
				statement.setString(5, authToken);
				statement.setString(6, email);
				statement.setInt(7, rating);
				statement.setString(8, street);
				statement.setString(9, town);
				statement.setString(10, zip);
				statement.setInt(11, state);

				int rows = statement.executeUpdate();
				
				// If there is a result, set inserted to true
				if(rows == 1){
					inserted = true;
				}

				statement.close();
			}
			catch(Exception e){
				System.err.println("MySQLDBPopulator (insertUser): Could not insert the user");
				e.printStackTrace();
			}
		}
		else{
			System.err.println("MySQLDBPopulator (insertUser): The database connection is not open");
		}
		
		return inserted;
	}
	
	public boolean dropAddItems(){
		boolean deleted = false;
		Connection conn = this.getConnection();
		
		if(conn != null){
			try{
				PreparedStatement dropStatement = conn.prepareStatement("DROP TABLE IF EXISTS `items`");
				
				PreparedStatement createStatement = conn.prepareStatement("CREATE TABLE IF NOT EXISTS `items` ( " +
						"`id` int(10) unsigned NOT NULL AUTO_INCREMENT, " +
						"`name` text NOT NULL, " +
						"`description` text, " +
						"`startPrice` float unsigned NOT NULL, " +
						"`quantity` int(10) unsigned NOT NULL, " +
						"`reservePrice` float unsigned NOT NULL DEFAULT '0', " +
						"`buyNowPrice` float unsigned DEFAULT NULL, " +
						"`noOfBids` int(10) unsigned NOT NULL DEFAULT '0', " +
						"`currentBid` float unsigned NOT NULL DEFAULT '0', " +
						"`startDate` datetime NOT NULL, " +
						"`endDate` datetime NOT NULL, " +
						"`sellerID` int(10) unsigned NOT NULL, " +
						"`categoryID` int(10) unsigned NOT NULL, " +
						"`maxBid` float(10) NOT NULL DEFAULT '0', " +
						"`currentWinner` int(10) unsigned DEFAULT NULL, " +
						"`thumbnail` text, " +
						"PRIMARY KEY (`id`), " +
						"KEY `userIDIndex` (`sellerID`), " +
						"KEY `categoryIDIndex` (`categoryID`), " +
						"FULLTEXT KEY `textSearchIndex` (`name`,`description`) " +
						") ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1");
				
				dropStatement.executeUpdate();
				createStatement.executeUpdate();
				
				deleted = true;
			}
			catch(Exception e){
				System.err.println("MySQLDBPopulator (deleteUsers): Could not delete the users");
				e.printStackTrace();
			}
		}
		else{
			System.err.println("MySQLDBPopulator (deleteUsers): The database connection is not open");
		}
		
		return deleted;
	}
	
	public boolean insertItem(String name, String description, int startPrice, int quantity, int reservePrice, int buyNowPrice, Date startDate, Date endDate, int sellerID, int categoryID){
		boolean inserted = false;
		Connection conn = this.getConnection();
		
		if(conn != null){
			try{
				// Create the SQL statement to insert the item
				PreparedStatement statement = conn.prepareStatement("INSERT INTO `items` (name, description, startPrice, quantity, reservePrice, buyNowPrice, noOfBids, currentBid, startDate, endDate, sellerID, categoryID, maxBid) "
						+ "VALUES (?, ?, ?, ?, ?, ?, 0, 0, ?, ?, ?, ?, 0)");
				statement.setString(1, name);
				statement.setString(2, description);
				statement.setInt(3, startPrice);
				statement.setInt(4, quantity);
				statement.setInt(5, reservePrice);
				statement.setInt(6, buyNowPrice);
				statement.setDate(7, startDate);
				statement.setDate(8, endDate);
				statement.setInt(9, sellerID);
				statement.setInt(10, categoryID);

				int rows = statement.executeUpdate();
				
				// If there is a result, set inserted to true
				if(rows == 1){
					inserted = true;
				}

				statement.close();
			}
			catch(Exception e){
				System.err.println("MySQLDBPopulator (insertItem): Could not insert the item");
				e.printStackTrace();
			}
		}
		else{
			System.err.println("MySQLDBPopulator (insertItem): The database connection is not open");
		}
		
		return inserted;
	}
	
	public boolean dropAddBids(){
		boolean deleted = false;
		Connection conn = this.getConnection();
		
		if(conn != null){
			try{
				PreparedStatement dropStatement = conn.prepareStatement("DROP TABLE IF EXISTS `bids`");
				
				PreparedStatement createStatement = conn.prepareStatement("CREATE TABLE IF NOT EXISTS `bids` ( " +
						"`id` int(10) unsigned NOT NULL AUTO_INCREMENT, " +
						"`userID` int(10) unsigned NOT NULL, " +
						"`itemID` int(10) unsigned NOT NULL, " +
						"`quantity` int(10) unsigned NOT NULL, " +
						"`bid` float unsigned NOT NULL, " +
						"`maxBid` float unsigned NOT NULL, " +
						"`bidDate` datetime NOT NULL, " +
						"PRIMARY KEY (`id`), " +
						"KEY `item` (`itemID`), " +
						"KEY `userIDIndex` (`userID`) " +
						") ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1");
				
				dropStatement.executeUpdate();
				createStatement.executeUpdate();
				
				deleted = true;
			}
			catch(Exception e){
				System.err.println("MySQLDBPopulator (deleteBids): Could not delete the bids");
				e.printStackTrace();
			}
		}
		else{
			System.err.println("MySQLDBPopulator (deleteBids): The database connection is not open");
		}
		
		return deleted;
	}
	
	public boolean insertBid(int userID, int itemID, int quantity, int bid, int maxBid, Date bidDate){
		boolean inserted = false;
		Connection conn = this.getConnection();
		
		if(conn != null){
			try{
				// Create the SQL statement to insert the user
				PreparedStatement statement = conn.prepareStatement("INSERT INTO `bids` (userID, itemID, quantity, bid, maxBid, bidDate) "
						+ "VALUES (?, ?, ?, ?, ?, ?)");
				statement.setInt(1, userID);
				statement.setInt(2, itemID);
				statement.setInt(3, quantity);
				statement.setInt(4, bid);
				statement.setInt(5, maxBid);
				statement.setDate(6, bidDate);

				int rows = statement.executeUpdate();
				
				// If there is a result, set inserted to true
				if(rows == 1){
					inserted = true;
				}

				statement.close();
			}
			catch(Exception e){
				System.err.println("MySQLDBPopulator (insertBid): Could not insert the bid");
				e.printStackTrace();
			}
		}
		else{
			System.err.println("MySQLDBPopulator (insertBid): The database connection is not open");
		}
		
		return inserted;
	}
}
